from itertools import count

import pandas as pd
from pandas import isnull


def get_execl(path):
    df = pd.read_excel(path,sheet_name=None)
    #print(len(df))
    for i in range(len(df)):
        if list(df)[i] != '目录':
            key = 'primary key('
            print("/n/*-------"+list(df)[i]+"-------*/")
            tables = pd.read_excel(path,sheet_name=str(list(df)[i]))
            print("create table"+ list(df)[i] + '(')
            for row in range(1,len(tables)):
                use_name = str(tables.values[row,0])     #字段名
                chin_name = str(tables.values[row,1])   #中文名
                falg = str(tables.values[row,2])         #是否为主键
                type = str(tables.values[row,3])        #字段格式
                type_num =  str(tables.values[row,4])    #大小
                falg_null  = str(tables.values[row,5])   #是否为空

                if chin_name == 'nan':
                    chin_name = ''
                if type=='datetime':
                    print(get_dateime(use_name,chin_name,falg,type,type_num,falg_null))
                else:
                    print(get_type(use_name,chin_name,falg,type,type_num,falg_null))
                if falg == 'Y':
                    key+=use_name+','
            print(key[:-1]+')')
            print(");")

def get_type(use_name,chin_name,falg,type,type_num,falg_null):
    if falg_null == '否':
        return use_name+'  ' + type+'('+ type_num +') notnull comment \''+ chin_name+'\' ,'
    else:
        return use_name + ' ' + type + '(' + type_num + ') comment \'' + chin_name + '\' ,'

def get_dateime(use_name,chin_name,falg,type,type_num,falg_null):
    if falg_null == '否':
        return use_name+'  ' + type + ' notnull comment \''+ chin_name+'\' ,'
    else:
        return use_name + ' ' + type + ' comment \'' + chin_name + '\' ,'



if __name__ == '__main__':
    get_execl('/home/dhz/working/2024/报销/word/数据库表格.xlsx')
